package com.mva.Vehicle_DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.mva.common.DateUtils;
import com.mva.database.DB_Pipe;

public class VehicleDB implements VehicleDAO
  {
   private Connection con = null;
	   
   public VehicleDB()
	 {
	 }

   /*******************************************************************************
	Vehicle reader methods
   *******************************************************************************/
   
   public boolean existsVehicle (int Stck_No)
	 {
	  try
		{
		 String exists = "SELECT Stck_No "     +
                           "FROM vehicle "     +
                          "WHERE Stck_No = ?"; 
         con                   = DB_Pipe.DB_Connect();
	     PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setInt(1, Stck_No);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean existsVehicle (String VIN)
	 {
	  try
		{
		 String exists = "SELECT VIN "     +
                           "FROM vehicle " +
                          "WHERE VIN = ?"; 
         con                   = DB_Pipe.DB_Connect();
	     PreparedStatement ps  = con.prepareStatement(exists);
		 ps.setString(1, VIN);
		 ResultSet         rs  = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
			DB_Pipe.DB_Disconnect(con);
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }
   
   public Vehicle getVehicle (int Stck_No)
	 {
	  try
		{
		 String sel = "SELECT Stck_No, "        +
                             "VIN, "            +
                             "Typ, "            +
                             "Vndr, "           +
                             "Mdl, "            +
                             "Yr, "             +
                             "Clr, "            +
                             "Desc, "           +
                             "Stat, "           +
                             "Dt_Buy, "         +
                             "Amt_Buy, "        +
                             "Miles_Buy, "      +
                             "User_Buy, "       +
                             "Typ_Warr, "       +
                             "Miles_Warr, "     +
                             "YN_Trade, "       +
                             "Vndr_Trade, "     +
                             "Mdl_Trade, "      +
                             "Yr_Trade, "       +
                             "Dt_Sell, "        +
                             "Amt_Sell, "       +
                             "Miles_Sell, "     +
                             "User_Sell "       +
	                    "FROM vehicle "         +
					   "WHERE Stck_No = ?"; 
            con                   = DB_Pipe.DB_Connect();
		    PreparedStatement ps  = con.prepareStatement(sel);
		    ps.setInt(1, Stck_No);
		    ResultSet         rs  = ps.executeQuery();
			if (rs.next())
			  {
		       String  VIN        = rs.getString("VIN");
			   String  Typ        = rs.getString("Typ");
		       String  Vndr       = rs.getString("Vndr");
		       String  Mdl        = rs.getString("Mdl");
		       int     Yr         = rs.getInt("Yr");
		       String  Clr        = rs.getString("Clr");
		       String  Desc       = rs.getString("Desc");
		       String  Stat       = rs.getString("Stat");
		       Date    Dt_Buy     = rs.getDate("Dt_Buy");
		       double  Amt_Buy    = rs.getDouble("Amt_Buy");
		       int     Miles_Buy  = rs.getInt("Miles_Buy");
		       String  User_Buy   = rs.getString("User_Buy");
		       String  Typ_Warr   = rs.getString("Typ_Warr");
		       int     Miles_Warr = rs.getInt("Miles_Warr");
		       String  YN_Trade   = rs.getString("YN_Trade");
		       String  Vndr_Trade = rs.getString("Vndr_Trade");
		       String  Mdl_Trade  = rs.getString("Mdl_Trade");
		       int     Yr_Trade   = rs.getInt("Yr_Trade");
		       Date    Dt_Sell    = rs.getDate("Dt_Sell");
		       double  Amt_Sell   = rs.getDouble("Amt_Sell");
		       int     Miles_Sell = rs.getInt("Miles_Sell");
		       String  User_Sell  = rs.getString("User_Sell");
		       Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Desc, Stat,
                                                Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
                                                Typ_Warr, Miles_Warr,
                                                YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
                                                Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
			   rs.close();
			   ps.close();
			   DB_Pipe.DB_Disconnect(con);
			   return v;
			  }
			else
			  {
			   rs.close();
			   ps.close();
			   DB_Pipe.DB_Disconnect(con);
			   return null;
			  }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }

   public Vehicle getVehicle (String VIN)
	 {
	  try
		{
		 String sel = "SELECT Stck_No, "        +
                             "VIN, "            +
                             "Typ, "            +
                             "Vndr, "           +
                             "Mdl, "            +
                             "Yr, "             +
                             "Clr, "            +
                             "Desc, "           +
                             "Stat, "           +
                             "Dt_Buy, "         +
                             "Amt_Buy, "        +
                             "Miles_Buy, "      +
                             "User_Buy, "       +
                             "Typ_Warr, "       +
                             "Miles_Warr, "     +
                             "YN_Trade, "       +
                             "Vndr_Trade, "     +
                             "Mdl_Trade, "      +
                             "Yr_Trade, "       +
                             "Dt_Sell, "        +
                             "Amt_Sell, "       +
                             "Miles_Sell, "     +
                             "User_Sell "       +
	                    "FROM vehicle "         +
					   "WHERE VIN = ?"; 
            con                   = DB_Pipe.DB_Connect();
		    PreparedStatement ps  = con.prepareStatement(sel);
		    ps.setString(1, VIN);
		    ResultSet         rs  = ps.executeQuery();
			if (rs.next())
			  {
		       int     Stck_No    = rs.getInt("Stck_No");
			   String  Typ        = rs.getString("Typ");
		       String  Vndr       = rs.getString("Vndr");
		       String  Mdl        = rs.getString("Mdl");
		       int     Yr         = rs.getInt("Yr");
		       String  Clr        = rs.getString("Clr");
		       String  Desc       = rs.getString("Desc");
		       String  Stat       = rs.getString("Stat");
		       Date    Dt_Buy     = rs.getDate("Dt_Buy");
		       double  Amt_Buy    = rs.getDouble("Amt_Buy");
		       int     Miles_Buy  = rs.getInt("Miles_Buy");
		       String  User_Buy   = rs.getString("User_Buy");
		       String  Typ_Warr   = rs.getString("Typ_Warr");
		       int     Miles_Warr = rs.getInt("Miles_Warr");
		       String  YN_Trade   = rs.getString("YN_Trade");
		       String  Vndr_Trade = rs.getString("Vndr_Trade");
		       String  Mdl_Trade  = rs.getString("Mdl_Trade");
		       int     Yr_Trade   = rs.getInt("Yr_Trade");
		       Date    Dt_Sell    = rs.getDate("Dt_Sell");
		       double  Amt_Sell   = rs.getDouble("Amt_Sell");
		       int     Miles_Sell = rs.getInt("Miles_Sell");
		       String  User_Sell  = rs.getString("User_Sell");
		       Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Desc, Stat,
                                                Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
                                                Typ_Warr, Miles_Warr,
                                                YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
                                                Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
			   rs.close();
			   ps.close();
			   DB_Pipe.DB_Disconnect(con);
			   return v;
			  }
			else
			  {
			   rs.close();
			   ps.close();
			   DB_Pipe.DB_Disconnect(con);
			   return null;
			  }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }
   
   public boolean getVehicles (ArrayList<Vehicle> vehicles)
	 {
	  try
		{
		 String qry = "SELECT Stck_No, "        +
		                     "VIN, "            +
		                     "Typ, "            +
		                     "Vndr, "           +
		                     "Mdl, "            +
		                     "Yr, "             +
		                     "Clr, "            +
		                     "Desc, "           +
		                     "Stat, "           +
		                     "Dt_Buy, "         +
		                     "Amt_Buy, "        +
		                     "Miles_Buy, "      +
		                     "User_Buy, "       +
		                     "Typ_Warr, "       +
		                     "Miles_Warr, "     +
		                     "YN_Trade, "       +
		                     "Vndr_Trade, "     +
		                     "Mdl_Trade, "      +
		                     "Yr_Trade, "       +
		                     "Dt_Sell, "        +
		                     "Amt_Sell, "       +
		                     "Miles_Sell, "     +
		                     "User_Sell "       +
			 	        "FROM vehicle "         +
				       "ORDER BY Stck_No ASC";
         con                   = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(qry);
		 ResultSet         rs  = ps.executeQuery();
		 while(rs.next())
		   {
		    int     Stck_No    = rs.getInt("Stck_No");
		    String  VIN        = rs.getString("VIN");
		    String  Typ        = rs.getString("Typ");
		    String  Vndr       = rs.getString("Vndr");
		    String  Mdl        = rs.getString("Mdl");
		    int     Yr         = rs.getInt("Yr");
		    String  Clr        = rs.getString("Clr");
		    String  Desc       = rs.getString("Desc");
		    String  Stat       = rs.getString("Stat");
		    Date    Dt_Buy     = rs.getDate("Dt_Buy");
		    double  Amt_Buy    = rs.getDouble("Amt_Buy");
		    int     Miles_Buy  = rs.getInt("Miles_Buy");
		    String  User_Buy   = rs.getString("User_Buy");
		    String  Typ_Warr   = rs.getString("Typ_Warr");
		    int     Miles_Warr = rs.getInt("Miles_Warr");
		    String  YN_Trade   = rs.getString("YN_Trade");
		    String  Vndr_Trade = rs.getString("Vndr_Trade");
		    String  Mdl_Trade  = rs.getString("Mdl_Trade");
		    int     Yr_Trade   = rs.getInt("Yr_Trade");
		    Date    Dt_Sell    = rs.getDate("Dt_Sell");
		    double  Amt_Sell   = rs.getDouble("Amt_Sell");
		    int     Miles_Sell = rs.getInt("Miles_Sell");
		    String  User_Sell  = rs.getString("User_Sell");
		    Vehicle v          = new Vehicle(Stck_No, VIN, Typ, Vndr, Mdl, Yr, Clr, Desc, Stat,
		    		                         Dt_Buy, Amt_Buy, Miles_Buy, User_Buy,
		    		                         Typ_Warr, Miles_Warr,
		    		                         YN_Trade, Vndr_Trade, Mdl_Trade, Yr_Trade,
		    		                         Dt_Sell, Amt_Sell, Miles_Sell, User_Sell);
		    vehicles.add(v);
		   }
		 rs.close();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	 }

   /*******************************************************************************
	Vehicle writer methods
   *******************************************************************************/
   
   public boolean addVehicle (Vehicle v)
	 {
	  try
		{
		 String ins = "INSERT "                     +
		                "INTO vehicle(Stck_No, "    +
                                     "VIN, "        +
                                     "Typ, "        +
                                     "Vndr, "       +
                                     "Mdl, "        +
                                     "Yr, "         +
                                     "Clr, "        +
                                     "Desc, "       +
                                     "Stat, "       +
                                     "Dt_Buy, "     +
                                     "Amt_Buy, "    +
                                     "Miles_Buy, "  +
                                     "User_Buy, "   +
                                     "Typ_Warr, "   +
                                     "Miles_Warr, " +
                                     "YN_Trade, "   +
                                     "Vndr_Trade, " +
                                     "Mdl_Trade, "  +
                                     "Yr_Trade, "   +
                                     "Dt_Sell, "    +
                                     "Amt_Sell, "   +
                                     "Miles_Sell, " +
                                     "User_Sell "   +
		                             "TMS_Crtd, "   +
		                             "User_Crtd, "  +
 		                             "TMS_Updt, "   +
		                             "User_Updt) "  +
				      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		 con                   = DB_Pipe.DB_Connect();
		 PreparedStatement ps  = con.prepareStatement(ins);
	     Timestamp         ts  = DateUtils.getTmstmp();
         ps.setInt(1, v.getStckNo());
         ps.setString(2, v.getVIN());
         ps.setString(3, v.getTyp());
         ps.setString(4, v.getVndr());
         ps.setString(5, v.getMdl());
         ps.setInt(6, v.getYr());
         ps.setString(7, v.getClr());
         ps.setString(8, v.getDesc());
         ps.setString(9, v.getStat());
         ps.setDate(10, v.getDtBuy());
         ps.setDouble(11, v.getAmtBuy());
         ps.setInt(12, v.getMilesBuy());
         ps.setString(13, v.getUserBuy());
         ps.setString(14, v.getTypWarr());
         ps.setInt(15, v.getMilesWarr());
         ps.setString(16, v.getYNTrade());
         ps.setString(17, v.getVndrTrade());
         ps.setString(18, v.getMdlTrade());
         ps.setInt(19, v.getYrTrade());
         ps.setDate(20, v.getDtSell());
         ps.setDouble(21, v.getAmtSell());
         ps.setInt(22, v.getMilesSell());
         ps.setString(23, v.getUserSell());
		 ps.setTimestamp(24, ts);
		 ps.setString(25, "USER0001");
		 ps.setTimestamp(26, ts);
		 ps.setString(27, "USER9999");
		 ps.executeUpdate();
         ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteVehicle (Vehicle v)
	 {
	  try
		{
		 String del = "DELETE "              +
                        "FROM vehicle "      +
                       "WHERE Stck_No = ?";
		 con                  = DB_Pipe.DB_Connect();
		 PreparedStatement ps = con.prepareStatement(del);
         ps.setInt(1, v.getStckNo());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateVehicle (Vehicle v)
	 {
	  try
		{
		 String upd = "UPDATE vehicle "         +
                         "SET VIN = ?, "        +
                             "Typ = ?, "        +
                             "Vndr = ?, "       +
                             "Mdl = ?, "        +
                             "Yr = ?, "         +
                             "Clr = ?, "        +
                             "Desc = ?, "       +
                             "Stat = ?, "       +
                             "Dt_Buy = ?, "     +
                             "Amt_Buy = ?, "    +
                             "Miles_Buy = ?, "  +
                             "User_Buy = ?, "   +
                             "Typ_Warr = ?, "   +
                             "Miles_Warr = ?, " +
                             "YN_Trade = ?, "   +
                             "Vndr_Trade = ?, " +
                             "Mdl_Trade = ?, "  +
                             "Yr_Trade = ?, "   +
                             "Dt_Sell = ?, "    +
                             "Amt_Sell = ?, "   +
                             "Miles_Sell = ?, " +
                             "User_Sell = ?, "  +
                             "TMS_Updt = ?, "   +
                             "User_Updt = ? "   +
                       "WHERE Stck_No = ?";
         con                  = DB_Pipe.DB_Connect();
		 PreparedStatement ps = con.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
         ps.setString(1, v.getVIN());
         ps.setString(2, v.getTyp());
         ps.setString(3, v.getVndr());
         ps.setString(4, v.getMdl());
         ps.setInt(5, v.getYr());
         ps.setString(6, v.getClr());
         ps.setString(7, v.getDesc());
         ps.setString(8, v.getStat());
         ps.setDate(9, v.getDtBuy());
         ps.setDouble(10, v.getAmtBuy());
         ps.setInt(11, v.getMilesBuy());
         ps.setString(12, v.getUserBuy());
         ps.setString(13, v.getTypWarr());
         ps.setInt(14, v.getMilesWarr());
         ps.setString(15, v.getYNTrade());
         ps.setString(16, v.getVndrTrade());
         ps.setString(17, v.getMdlTrade());
         ps.setInt(18, v.getYrTrade());
         ps.setDate(19, v.getDtSell());
         ps.setDouble(20, v.getAmtSell());
         ps.setInt(21, v.getMilesSell());
         ps.setString(22, v.getUserSell());
         ps.setTimestamp(23, ts);
		 ps.setString(24, "USER8888");
	     ps.setInt(25, v.getStckNo());
		 ps.executeUpdate();
		 ps.close();
		 DB_Pipe.DB_Disconnect(con);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }
  }